﻿using System;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;

namespace RPAAction.Data_CSO
{
    public class NPOIExcelDataReader : RPADataReader
    {
        public override int FieldCount => headerCount;

        public override bool HasRows => currentRowNum <= LastRowNum;

        public override bool IsClosed => isClosed;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheet">工作表名称,如果为空则取第一个</param>
        /// <exception cref="Exception"></exception>
        public NPOIExcelDataReader(string filePath, string sheet)
        {
            fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

            string fileExtension = Path.GetExtension(filePath).ToLower();
            if (object.Equals(".xls", fileExtension))
            {
                wb = new HSSFWorkbook(fs);
            }
            else if (object.Equals(".xlsx", fileExtension))
            {
                wb = new XSSFWorkbook(fs);
            }
            else
            {
                throw new Exception("File format error, only support xlsx and xls");
            }

            if (string.IsNullOrWhiteSpace(sheet))
            {
                sh = wb.GetSheetAt(0);
            }
            else
            {
                sh = wb.GetSheet(sheet);
            }

            headerRow = sh.GetRow(sh.FirstRowNum);
            headerCount = headerRow.LastCellNum;

            currentRowNum = sh.FirstRowNum;
            LastRowNum = sh.LastRowNum;
        }

        public override string GetName(int ordinal)
        {
            return ICellToObject(headerRow.GetCell(ordinal)).ToString();
        }

        public override object GetValue(int ordinal)
        {
            return ICellToObject(currentRow.GetCell(ordinal));
        }

        public override bool Read()
        {
            ++currentRowNum;
            if (currentRowNum <= LastRowNum)
            {
                currentRow = sh.GetRow(currentRowNum);
                return true;
            }
            else
            {
                return false;
            }
        }

        public override void Close()
        {
            base.Close();

            fs.Dispose();
            wb?.Close();
            wb = null;
            sh = null;
            headerRow = null;
            currentRow = null;
            isClosed = true;
        }

        private FileStream fs;
        private IWorkbook wb;
        private ISheet sh;
        private IRow headerRow;
        private IRow currentRow;
        private int headerCount;
        private int currentRowNum;
        private readonly int LastRowNum;
        private bool isClosed = false;

        private static object ICellToObject(ICell cell)
        {
            if (cell is null)
            {
                return null;
            }
            switch (cell.CellType)
            {
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Boolean:
                    return cell.BooleanCellValue;
                case CellType.Blank:
                case CellType.Error:
                    return null;
                case CellType.Formula:
                case CellType.Numeric:
                    switch (cell.CellStyle.DataFormat)
                    {
                        case 14: // 日期格式：14（例如，2023/01/30）
                            return cell.DateCellValue.ToString("yyyy/MM/dd");
                        case 20: // 时间格式：20（例如，13:45:30）
                            return cell.DateCellValue.ToString("HH:mm:ss");
                        case 22: // 日期-时间格式：20（例如，2023/01/30 13:45:30）
                            return cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                        default:
                            try
                            {
                                return cell.NumericCellValue;
                            }
                            catch (InvalidOperationException)
                            {
                                return cell.StringCellValue;
                            }
                    }
                case CellType.Unknown:
                default:
                    return cell.ToString();
            }
        }
    }
}
